﻿
CREATE PROCEDURE [dbo].[balcon_GenNotaContabila]
	@idLista int,
	@cont TipCont,
	@CodUnitate smallint,
	@DeLaData datetime,
	@PanaLaData datetime,
	@An smallint,
	@iOrder smallint,
	@contDebit TipCont,
	@contCredit TipCont
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @strContStart as TipCont,@strContEnd as TipCont
	DECLARE @tipb as varchar(100)
	DECLARE @oct as varchar(100)
	DECLARE @jurnal int
	SET @strContStart=''
	SET @strContEnd=''
	SET @jurnal= -1
	SELECT @tipb=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='JRN' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate] = @CodUnitate
	if @tipb<>''
		SELECT @jurnal = IDJurnal FROM [Jurnale] WHERE Codificare=@tipb
	
	SET @oct=''
	SELECT @oct=Valoare FROM [ParamListe] WHERE IDLista=@idLista AND Cod='OCT' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate] = @CodUnitate
	

    SELECT @strContStart=LTRIM(RTRIM(Valoare)) FROM [ParamListe] WHERE IDLista=@idLista AND Cod='DLC' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate] = @CodUnitate
    SELECT @strContEnd=LTRIM(RTRIM(Valoare)) FROM [ParamListe] WHERE IDLista=@idLista AND Cod='PLC' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate] = @CodUnitate

	if @An<=0
	SELECT b.NumarDoc, b.DataDoc, b.NumarNota, a.ContDebit, a.ContCredit, 
           b.IDJurnal, a.ValoareLei, a.DataDocJust, a.TipDocJust, a.NumarDocJust, a.ExplicatiiDocJust, 
           b.TipFormula, c.Categorie, c.Tip, a.CodUnitate,a.IDDocument
	FROM  [Rulaje] a 
	      INNER JOIN  [RulajeDoc] b ON a.IDDocument = b.IDDocument 
	      INNER JOIN  [Conturi] c ON a.ContDebit = c.Cont AND c.CodUnitate = a.CodUnitate
		  WHERE (a.CodUnitate = @CodUnitate) 
				 AND ((UPPER(@oct)='DEBIT' AND (@strContStart='' OR a.ContDebit>=@strContStart) AND (@strContEnd='' OR a.ContDebit<=@strContEnd)) 
				 OR (UPPER(@oct)='CREDIT' AND (@strContStart='' OR a.ContCredit>=@strContStart) AND (@strContEnd='' OR a.ContCredit<=@strContEnd))
				 OR (UPPER(@oct)=''))
				 AND (@cont='' OR (a.ContDebit = @cont OR a.ContCredit =@cont))
				 AND (b.DataDoc >= @DeLaData AND b.DataDoc<= @PanaLaData)
				 AND (@jurnal=-1 OR b.IDJurnal=@jurnal)
				 AND (@contDebit='' OR (a.ContDebit = @contDebit))
				 AND (@contCredit='' OR (a.ContCredit =@contCredit))

	ORDER BY CASE WHEN @iOrder = 1 THEN ContDebit 
	              WHEN @iOrder = 2 THEN ContCredit 
	              ELSE CAST(b.DataDoc AS VARCHAR(50)) END
	ELSE
	SELECT b.NumarDoc, b.DataDoc, b.NumarNota, a.ContDebit, a.ContCredit, 
           b.IDJurnal, a.ValoareLei, a.DataDocJust, a.TipDocJust, a.NumarDocJust, a.ExplicatiiDocJust, 
           b.TipFormula, c.Categorie, c.Tip, a.CodUnitate,a.IDDocument
	FROM  [arhRulaje] a 
	      INNER JOIN  [arhRulajeDoc] b ON a.IDDocument = b.IDDocument and a.An=@An
	      INNER JOIN  [arhConturi] c ON a.ContDebit = c.Cont AND c.CodUnitate = a.CodUnitate and a.An =c.An
		  WHERE (a.CodUnitate = @CodUnitate)
				 AND ((UPPER(@oct)='DEBIT' AND (@strContStart='' OR a.ContDebit>=@strContStart) AND (@strContEnd='' OR a.ContDebit<=@strContEnd)) 
				 OR (UPPER(@oct)<>'DEBIT' AND (@strContStart='' OR a.ContCredit>=@strContStart) AND (@strContEnd='' OR a.ContCredit<=@strContEnd))
				 OR (UPPER(@oct)=''))
				 AND (@cont='' OR (a.ContDebit = @cont OR a.ContCredit =@cont))
				 AND (b.DataDoc >= @DeLaData AND b.DataDoc<= @PanaLaData)
				 AND (@jurnal=-1 OR b.IDJurnal=@jurnal)
				 AND (@contDebit='' OR (a.ContDebit = @contDebit))
				 AND (@contCredit='' OR (a.ContCredit =@contCredit))

	ORDER BY CASE WHEN @iOrder = 1 THEN ContDebit 
	              WHEN @iOrder = 2 THEN ContCredit 
	              ELSE CAST(b.DataDoc AS VARCHAR(50)) END


END